如果要製作月報...但只有給起訖日
要產生出如下的列表 要怎麼辦...
第一個想到的解法 會使用 WHILE + [暫存表]
迴圈遍歷 把每個月新增入暫存表中
程式碼如下:
DECLARE @t TABLE
(
StartDate DATETIME,
EndDate DATETIME
);
INSERT INTO @t
( StartDate, EndDate )
VALUES ( '2017/01/01', -- StartDate - datetime
'2018/01/01' -- EndDate - datetime
);
--宣告一個起始時間變數
DECLARE @TempStartDate DATETIME
DECLARE @TempEndDate DATETIME
--設置變數 最小時間(起始時間) 和 最大時間
SELECT @TempStartDate = StartDate,@TempEndDate=EndDate
FROM @t
CREATE TABLE #TEMP(Dates DATETIME)
WHILE(@TempStartDate < @TempEndDate)
BEGIN
--將資料新增入暫存表
INSERT INTO #TEMP (Dates) VALUES (@TempStartDate)
--每跑一次迴圈就加一個月
SELECT @TempStartDate = DATEADD(MONTH,1,@TempStartDate)
END
SELECT * FROM #TEMP
DROP TABLE #TEMP
但這個解法雖然簡單..但程式碼又臭又長..
Q: 有沒有更好看的解法又可達成目的呢?
ANS: 有!! 就是本次主角 CTE 遞迴
話不多說先貼上程式碼
DECLARE @t TABLE
(
StartDate DATETIME,
EndDate DATETIME
);
INSERT INTO @t
( StartDate, EndDate )
VALUES ( '2017/01/01', -- StartDate - datetime
'2018/01/01' -- EndDate - datetime
);
;WITH CTE (Dates,EndDate) AS
(
SELECT StartDate AS Dates,EndDate AS EndDate
FROM @t
UNION ALL --注意這邊使用 UNION ALL
SELECT DATEADD(MONTH,1,Dates),EndDate
FROM CTE
WHERE DATEADD(MONTH,1,Dates) < EndDate --判斷是否目前遞迴月份小於結束日期
)
SELECT CTE.Dates
FROM CTE
接下來解說 CTE遞迴原理 :
可看到CTE中最主要執行四個步驟
使用CTE遞迴必須使用UNION ALL
最後CTE結果集就會呈現如下^^
此文同步發布在 : https://dotblogs.com.tw/daniel/2018/03/18/213231
補充 oracle解法
同場加映!!
如果使用 oracle 可使用 connect by
很簡便取得日曆
CREATE TABLE T
(
StartDate DATE,
EndDate DATE
);
INSERT INTO T( StartDate, EndDate ) VALUES (date '2017-01-01',date '2018-01-01');
select add_months(trunc(StartDate,'mm'),level - 1 ) "Date"
from T
connect by trunc(EndDate,'mm') >= add_months(trunc(StartDate,'mm'),level)
order by 1
用Postgresql 輕鬆愉快多了.
select dates
from generate_series('2017-01-01 00:00:00'::timestamp,
'2017-12-01 00:00:00'::timestamp,
'1 months') as gs(dates);
dates
---------------------
2017-01-01 00:00:00
2017-02-01 00:00:00
2017-03-01 00:00:00
2017-04-01 00:00:00
2017-05-01 00:00:00
2017-06-01 00:00:00
2017-07-01 00:00:00
2017-08-01 00:00:00
2017-09-01 00:00:00
2017-10-01 00:00:00
2017-11-01 00:00:00
2017-12-01 00:00:00
(12 rows)
Postgresql 用起來真的很簡便 謝謝分享
Oracle有幾個方法比較少見
分享如下
供參考
SELECT ADD_MONTHS(TO_DATE('2017/01/01', 'YYYY/MM/DD'), rownum - 1)
FROM dual
CONNECT BY rownum <=
MONTHS_BETWEEN(TO_DATE('2018/01/01', 'YYYY/MM/DD'),
TO_DATE('2017/01/01', 'YYYY/MM/DD'));
SELECT DATES FROM (SELECT TO_DATE('2017/01/01', 'YYYY/MM/DD') DT FROM DUAL)
MODEL
DIMENSION BY (0 D) MEASURES (DT DATES)
RULES ITERATE (1000) UNTIL (DATES[ITERATION_NUMBER+1]>= ADD_MONTHS(TO_DATE('2018/01/01', 'YYYY/MM/DD'),-1))
(
DATES[ITERATION_NUMBER+1] = ADD_MONTHS(DATES[ITERATION_NUMBER],1)
);
CREATE OR REPLACE PACKAGE XX_PIPL_PKG AS
TYPE DATE_TBL_TYPE IS TABLE OF DATE;
FUNCTION DATE_PIPL(V_START_DATE DATE, V_END_DATE DATE) RETURN DATE_TBL_TYPE
PIPELINED;
END XX_PIPL_PKG;
CREATE OR REPLACE PACKAGE BODY XX_PIPL_PKG AS
FUNCTION DATE_PIPL(V_START_DATE DATE, V_END_DATE DATE) RETURN DATE_TBL_TYPE
PIPELINED IS
BEGIN
FOR i IN 1 .. MONTHS_BETWEEN(V_END_DATE, V_START_DATE) LOOP
PIPE row(ADD_MONTHS(V_START_DATE, i - 1));
END LOOP;
RETURN;
END;
END XX_PIPL_PKG;
SELECT COLUMN_VALUE AS PERIOD_CODE
FROM TABLE(XX_PIPL_PKG.DATE_PIPL(TO_DATE('2017/01/01', 'YYYY/MM/DD'),
TO_DATE('2018/01/01', 'YYYY/MM/DD')));
感謝大大補充~